/**
 * Author: ZhangYang
 * Version: 1.01
 */
package com.pccw.portlet.util.excel;

import java.util.List;
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;

public class ExcelUtil {
	public static final short EXCEL_FORMAT_CODE_OF_TEXT = 49; // �����ж�EXCEL��Ԫ��ĸ�ʽ�Ƿ�Ϊ���ı���
	public static final short EXCEL_FORMAT_CODE_OF_GENERAL = 0; // �����ж�EXCEL��Ԫ��ĸ�ʽ�Ƿ�Ϊ�����桱
	public static final short EXCEL_FORMAT_CODE_OF_DATE = 14; // �����ж�EXCEL��Ԫ��ĸ�ʽ�Ƿ�Ϊ�����ڡ�
    // excel styles
	public static final short FGColor_Null=9;
	public static final short FGColor_Green = 42;
	public static final short FGColor_Yellow = 13;
	public static final short FGColor_LightYellow = 43;
	public static final short FGColor_Red = 10;
	public static final short BGColor = 64;
	public static final short FPattern = 1;
	public static final short BorderBottom = 1;
	public static final short BorderTop = 1;
	public static final short BorderLeft = 1;
	public static final short BorderRight = 1;
	public static final short LeftBorderColor = 64;
	public static final short RightBorderColor = 64;
	public static final short TopBorderColor = 64;
	public static final short BottomBorderColor = 64;
	public static final short Alignment = 1;
	public static final short Vertical = 2;
	
	public static final short Align = 2;
	public static final short Border = 2;
	public static final short FontHeightLow = 10;
	public static final short FontHeight = 13;
	public static final String FontName = "Arial";
	
	public static final int MAX_ROW_ID_IN_SHEET = 65535; // һ��Sheet��֧�ֵ�����к�
	public static final short MAX_COLUMN_ID_IN_SHEET = 255; // һ��Sheet��֧�ֵ�����к�
	
	public static final short COMMON_DEFAULT_ROW_HEIGHT = 350; // ���õ�Ĭ���иߣ���λΪ�� 1/20 of a point��
	public static final short COMMON_DEFAULT_COLUMN_WIDTH = 25; // ���õ�Ĭ���п?��λΪ��  һ���ַ��ȣ�
	public static final short COMMON_MAX_COLUMN_WIDTH = 25600; // ���õ�����п?��λΪ�� һ���ַ��ȵ�1/256��
    
    /**
     * ���ó�����ʾ��Ԫ��ĸ�ʽ
     */
    public static HSSFCellStyle configErrorStyle(HSSFCellStyle style, short FGColor){
    	style.setFillForegroundColor(FGColor);
    	style.setFillBackgroundColor(BGColor);
    	style.setFillPattern(FPattern);
    	style.setDataFormat((short)49);
    	style.setBorderBottom(BorderBottom);
    	style.setBorderTop(BorderTop);
    	style.setBorderLeft(BorderLeft);
    	style.setBorderRight(BorderRight);
    	style.setLeftBorderColor(LeftBorderColor);
    	style.setRightBorderColor(RightBorderColor);
    	style.setTopBorderColor(TopBorderColor);
    	style.setBottomBorderColor(BottomBorderColor);
    	style.setAlignment(Alignment);
    	style.setVerticalAlignment(Vertical);
    	style.setDataFormat((short)0);
    	style.setWrapText(true);
    	
    	return style;
    }
    
    /**
     * ����һ����Ԫ��ĸ�ʽ
     */
    public static void setCellStyle(HSSFCell cell, HSSFCellStyle style){
    	// Ӧ�ñ��ָõ�Ԫ��ԭ�е������С���Ժ������Ӵ˹��ܡ�(*)
    	cell.setCellStyle(style);
    }

	/**
	 * �ж�һ����Ԫ��ĸ�ʽ�Ƿ�Ϊ"����"
	 * Added on 2007-7-20
	 */
	public static boolean isCellFormatGeneral(HSSFCell cell){
		if(cell == null){
			return false;
		}
		
		int cellFormatCode = cell.getCellStyle().getDataFormat();
		
		if(cellFormatCode == EXCEL_FORMAT_CODE_OF_GENERAL){
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * �ж�һ����Ԫ��ĸ�ʽ�Ƿ�Ϊ"�ı�"
	 * Added on 2007-7-20
	 */
	public static boolean isCellFormatText(HSSFCell cell){
		if(cell == null){
			return false;
		}
		
		int cellFormatCode = cell.getCellStyle().getDataFormat();
		
		if(cellFormatCode == EXCEL_FORMAT_CODE_OF_TEXT){
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * �ж�һ����Ԫ��ĸ�ʽ�Ƿ�Ϊ"����"
	 * Added on 2007-7-20
	 */
	public static boolean isCellFormatDate(HSSFCell cell){
		if(cell == null){
			return false;
		}
		
		int cellFormatCode = cell.getCellStyle().getDataFormat();
		
		if(cellFormatCode == EXCEL_FORMAT_CODE_OF_DATE){
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * �жϸõ�Ԫ�����ݵĳ����Ƿ񳬹�ָ��������
	 */
	public static boolean isExceedCellLengthLimit(HSSFCell cell, int limit){
		if(cell == null){
			return false;
		}
		
		String cellValue = getStringCellValue(cell);
		if ( cellValue == null ){
			return false;
		}
		else if( cellValue.getBytes().length > limit ) {
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * �жϸõ�Ԫ�����ݵĳ����Ƿ񳬹�ָ��������
	 */
	public static boolean isLessThanCellLengthRequirement(HSSFCell cell, int minLength){
		if(cell == null){
			return false;
		}
		
		String cellValue = getStringCellValue(cell);
		if ( cellValue == null ){
			return false;
		}
		else if( cellValue.getBytes().length < minLength ) {
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * ���һ��EXCEL Row������кţ��õ�һ����Ԫ��HSSFCell�������õ�Ԫ����ڣ����䷵��
	 * �����ڣ��򴴽�һ���µ�Ԫ��
	 */
	public static HSSFCell getCell(HSSFRow row, short i) {
		if(row == null || i < 0 || i > MAX_COLUMN_ID_IN_SHEET){
			return null;
		}
		
		HSSFCell cell = row.getCell(i);
		if(cell == null){
			cell = row.createCell(i);
		}
        
        return cell;
    }
	
	/**
	 * ���һ��EXCEL Sheet������кš��кţ��õ�һ����Ԫ��HSSFCell�������õ�Ԫ����ڣ����䷵��
	 * �����л�õ�Ԫ�񲻴��ڣ��򴴽�һ�����л��µ�Ԫ��
	 */
	public static HSSFCell getCell(HSSFSheet sheet, int rowId, short columnId) {
		if(sheet == null || rowId < 0 || columnId < 0){
			return null;
		}
		
		HSSFRow row = sheet.getRow(rowId);
		if(row == null){
			row = sheet.createRow(rowId);
		}
		
		HSSFCell cell = row.getCell(columnId);
		if(cell == null){
			cell = row.createCell(columnId);
		}
        
        return cell;
    }
	
	/**
	 * ���һ��EXCEL Sheet������кţ��õ�һ����HSSFRow���������д��ڣ����䷵��
	 * �����ڣ��򴴽�һ������
	 */
	public static HSSFRow getRow(HSSFSheet sheet, int rowId) {
		if(sheet == null || rowId < 0 || rowId > MAX_ROW_ID_IN_SHEET){
			return null;
		}
		
		HSSFRow row = sheet.getRow(rowId);
		if(row == null){
			row = sheet.createRow(rowId);
		}
        
        return row;
    }
	
	/**
	 * ���һ��EXCEL Workbook�����sheet��ţ��õ�һ����HSSFSheet��������sheet���ڣ����䷵��
	 * �����ڣ��򴴽�һ����sheet
	 */
	public static HSSFSheet getSheet(HSSFWorkbook workbook, int sheetId) {
		if(workbook == null || sheetId < 0){
			return null;
		}
		
		HSSFSheet sheet = workbook.getSheetAt(sheetId);
		if(sheet == null){
			sheet = workbook.createSheet("sheet" + sheetId);
		}
        
        return sheet;
    }
	
	/**
	 * �õ���Ԫ�����ݵ��ַ�ֵ
	 * @throws UnSupportedCellTypeException 
	 */
	public static String getStringCellValue(HSSFCell cell) {
		if (cell == null){
			return "";
		}

		String result = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_BOOLEAN:
				result = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					java.text.SimpleDateFormat TIME_FORMATTER = new java.text.SimpleDateFormat(
							"yyyy-MM-dd");
					result = TIME_FORMATTER.format(cell.getDateCellValue());
				}
				else{
					// ���ַ�ʽ�п��ܻ�ʹԭ���뵱���ַ��������һ������ĩβ����".0"
					// �Ժ��ٿ����Ƿ���Ҫ��(*)
					double doubleValue = cell.getNumericCellValue();
					result = "" + doubleValue;
					
					// ���´�����ܻ����EXCEL��Ԫ�������ֵ��Զ���������
					/*
					DecimalFormat format = (DecimalFormat) NumberFormat
							.getPercentInstance();
					format.applyPattern("#####0");
					return trim(format.format(cell.getNumericCellValue()));
					*/
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				if (cell.getRichStringCellValue() == null){
					result = "";
				}
				else{
					result = trim(cell.getRichStringCellValue().getString());
				}
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				result = "";
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				result = "";
				break;
			default:
				// ˵�����ڲ�֧�ֵĵ�Ԫ���ʽ
				result = "";
				//throw new UnSupportedCellTypeException("");
		}
		
		return result;
	}
	
	/**
	 * �жϵ�Ԫ���Ƿ��й�ʽ
	 */
	public static boolean isFormulaExistsInCell(HSSFCell cell){
		boolean result = false;
		if(cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
			result = true;
		}
		
		return result;
	}
	
	/**
	 * �жϵ�Ԫ��ĸ�ʽ�Ƿ���
	 */
	public static boolean isCellTypeSupported(HSSFCell cell){
		if(cell == null){
			return true;
		}
		
		boolean result = false;
		
		int cellType = cell.getCellType();
		
		if(cellType == HSSFCell.CELL_TYPE_STRING || cellType == HSSFCell.CELL_TYPE_NUMERIC 
				|| cellType == HSSFCell.CELL_TYPE_BOOLEAN || cellType == HSSFCell.CELL_TYPE_BLANK){
			result = true;
		}
		
		return result;
	}
	
	/**
	 * ��һ��double���͵���ֵת��Ϊ�ַ���ȥ��С��㼰�����0
	 */
	public static String getIntStringFromDouble(double d){
		String str = d + "";
		if(str.indexOf(".") >= 0){
			str = str.substring(0, str.lastIndexOf("."));
		}
		return str;
	}
	
	/**
	 * ��һ��double���͵���ֵת��Ϊint����
	 */
	public static int getIntFromDouble(double d){
		String str = d + "";
		if(str.indexOf(".") >= 0){
			str = str.substring(0, str.lastIndexOf("."));
		}
		
		int result = 0;
		try{
			result = Integer.parseInt(str);
		}
		catch(Exception e){
		}
		
		return result;
	}

	/**
	 *  �ж�һ���ַ��Ƿ�Ϊ���ָ�ʽ
	 */
	public static boolean isNumber(String str) {
		if(str == null || "".equals(str.trim())){
			return false;
		}
		
		try {
			Double.parseDouble(str);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	/**
	 * �ж�һ���ַ��Ƿ�Ϊ��׼���ڸ�ʽ
	 */
	public static boolean isStandardDate(String str) {
		if(str == null || "".equals(str.trim())){
			return false;
		}
		
		boolean flag = false;
		
		/*
		if(str.matches("[1-9][0-9]{3}\\-(0[0-9]|1[0-2])\\-(0[0-9]|1[0-9]|2[0-9]|3[0-1])")){
			flag = true;
		}
		*/
		
		// ���������ڸ�ʽ�Ƿ���ȷ
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		
		// �����������ſ�ķ�ʽ�����Ϊtrue�����������ڵ�����ʱ���Զ���λ���罫1993-2-29��Ϊ1993-3-1��
		sdf.setLenient(false); 
		
		Date date = null;
		try {
			date = sdf.parse(str);
		} catch (ParseException e) {
			flag = false;
		}
		
		if(date == null){
			flag = false;
		}
		else{
			flag = true;
		}
		
		return flag;
	}
	
	/**
	 * A safety method instead of String.trim()
	 */
	public static String trim(String str) {
		if(str == null){
			return null;
		}
		else{
			return str.trim();
		}
	}
	
	/**
	 * �ж�һ���ַ��Ƿ�Ϊ��
	 */
	public static boolean isEmptyString(String str) {
		if (str == null) {
			return true;
		}
		else if ("".equals(str.trim())) {
			return true;
		}
		else{
			return false;
		}
	}
	
	/**
	 * �ж�һ��ֵ�Ƿ����������ȡֵ���б��У����飩
	 */
	public static boolean isValueInOptions(Object value, Object[] options){
		boolean result = false;
		
		if(options != null && options.length > 0){
			for(int i = 0; i < options.length; i++ ){
				if(value == null){
					if(options[i] == null){
						result = true;
						break;
					}
				}
				else if(value.equals(options[i])){
					result = true;
					break;
				}
			}
		}
		
		return result;
	}
	
	/**
	 * �ж�һ��ֵ�Ƿ����������ȡֵ���б��У�List��
	 */
	public static boolean isValueInOptions(Object value, List options){
		boolean result = false;
		
		if(options != null && options.size() > 0){
			for(int i = 0; i < options.size(); i++ ){
				if(value == null){
					if(options.get(i) == null){
						result = true;
						break;
					}
				}
				else if(value.equals(options.get(i))){
					result = true;
					break;
				}
			}
		}
		
		return result;
	}
	
	/**
	 * �ж�һ��boolean�������Ƿ�����Ԫ�ض�Ϊtrue
	 */
	public static boolean isAllFlagTrue(boolean[] flags){
		boolean result = true;
		
		if(flags == null || flags.length == 0){
			return false;
		}
		
		for(int i = 0; i < flags.length; i++){
			if(flags[i] == false){
				result = false;
			}
		}
		
		return result;
	}
}
